from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, GradientFill, PatternFill
from .WordDB import WordSql


class Matrix():
    def __init__(self, path, uerid):
        self.path = path
        self.useid = uerid
        self.wb = Workbook()  # 工作薄
        self.ws = self.wb.active  # 工作表
        self.ws.title = 'HML'  # 工作表的标题
        self.num_str_dict = {}  # 列名字典
        self.course_content = []  # 存所有课程名称和毕业要求
        # self.major = major # 专业名称
        self.index_point_sum = {}  # 每个一级指标点下的二级指标点的最大值
        self.data_list = []  # 存所有指标点
        self.idx_poi_col = {}  # 二级指标点对应列
        self.idx_cour_sum = {}  # 二级指标点对应课程数量

    # 生成列名字典，只是为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
    def Get_num_colnum_dict(self):
        '''
        :return: 返回字典：{1:'A', 2:'B', ...... , 52:'AZ'}
        '''
        A_Z = [chr(a) for a in range(ord('A'), ord('Z') + 1)]  # 将A~Z存在该列表内
        AA_AZ = ['A' + chr(a) for a in range(ord('A'), ord('Z') + 1)]  # 将AA~AZ存在该列表内
        BB_BZ = ['B' + chr(a) for a in range(ord('B'), ord('Z') + 1)]  # 将BB~BZ存在该列表内
        A_BZ = A_Z + AA_AZ + BB_BZ
        for i in A_BZ:
            self.num_str_dict[A_BZ.index(i) + 1] = i
        return self.num_str_dict

    # 获取每一个工作表中的数据
    def GetCourseData(self,year):

        # 从数据库获取上传文件路径
        wdsql = WordSql('127.0.0.1', 'root', 'zx.123456', 3306, 'word', 'utf8')
        # 查找当前上传的最近的文件的路径
        sql = "select coursepath from coursetemplates order by coursetemplatesid={year};".format(year = year)
        path = wdsql.get_one(sql)
        if path == None:
            sql = "select coursepath from coursetemplates order by year = {year} and userid = {userid};".format(year = year,userid = self.useid)
            path = wdsql.get_one(sql)

        flag = 1
        res = ""
        for i in str(path):
            if i == "'" and flag == 1:
                flag += 1
                res += "'"
            elif i == "'" and flag == 2:
                res += "'"
                break
            elif flag == 2:
                res += i
        res = res.split("'")[1]
        print("res", res)

        course_wb = load_workbook(res)
        for ipage in range(len(course_wb.worksheets)):  # 枚举每一个工作表
            sheet = course_wb.worksheets[ipage]
            # course_name_index,grad_req_index定位课程名称和毕业要求的下标   col用来记录列数
            course_name_index = -1
            grad_req_index = -1
            col = 1
            for i, data in enumerate(sheet.values):
                if i == 0:
                    for t in data:  # 枚举第一行中的课程名称和毕业要求在哪一列
                        if t == '课程名称':
                            course_name_index = col
                        elif t == '毕业要求':
                            grad_req_index = col
                        col += 1
            # 规定查找范围
            rows = sheet.iter_rows(min_col=sheet.min_column, max_col=sheet.max_column, min_row=2, max_row=sheet.max_row)
            for row in rows:
                temp = []
                for cell in row:
                    if cell.column == course_name_index:
                        temp.append(cell.value)
                    if cell.column == grad_req_index:
                        temp.append(cell.value)
                if grad_req_index == -1:
                    temp.append(None)
                self.course_content.append(temp)

    # 求二级指标点个数
    def Get_index_point(self):
        # 求data_list
        for data in self.course_content:
            for idx, idx_data in enumerate(data):
                if idx == 1:
                    if idx_data == None:
                        continue
                    else:
                        str_idx = ""
                        for char_idx in idx_data:
                            if char_idx == ',' or char_idx == 'H' or char_idx == 'M' or char_idx == 'L':
                                if str_idx != "":
                                    self.data_list.append(str_idx)
                                str_idx = ""
                                continue
                            else:
                                str_idx += char_idx
                        if str_idx != "":
                            self.data_list.append(str_idx)
        # 求index_point_sum
        # 初始化
        for i in range(1, 13):
            self.index_point_sum[str(i)] = '0'
        # 求最大值
        for j in self.data_list:
            str_list = j.split('.')
            str1 = str_list[0]
            str2 = str_list[1]
            if self.index_point_sum[str1] < str2:
                self.index_point_sum[str1] = str2

    # 添加表格内容
    def AddContent(self):
        # 添加第一行数据
        self.Add_row()

        # 将第二行数据记录到x中  然后append到ws中
        x = []
        x.append('')
        for number in self.index_point_sum:
            for ind in range((int(self.index_point_sum[number]))):
                x.append(str(ind + 1))
        self.ws.append(x)

        # 将二级指标点对应列求得
        col = 2
        for fir_idx in range(1, 13):
            for sec_idx in range(1, (int(self.index_point_sum[str(fir_idx)])) + 1):
                tem_str = str(fir_idx) + '.' + str(sec_idx)
                self.idx_poi_col[tem_str] = col
                self.idx_cour_sum[tem_str] = 0
                col += 1

        # 添加HML
        r = 3
        for i in self.course_content:
            idx = 0
            tt = ""
            if i[1] == None:
                continue
            else:
                for j in i:
                    # print("j:",type(j),j)
                    if idx == 0:
                        self.ws.append(list([j]))
                        idx += 1
                    else:
                        if isinstance(j, str):
                            # ws.cell(row=r,column=)
                            # print(j)
                            data = j.split(',')
                            # print(data)
                            for k in data:
                                l = len(k)
                                # print(l)
                                s = k[0:l - 1:1]
                                # print(s)
                                da = k[l - 1:l:1]
                                # print(da)
                                self.idx_cour_sum[s] += 1
                                self.ws.cell(row=r, column=self.idx_poi_col[s], value=da)
                                t1 = str(r)
                                t = self.num_str_dict[self.idx_poi_col[s]] + t1
                                # print(t)
                                if da == 'H':
                                    self.ws[t].fill = PatternFill(patternType='solid', fgColor='feac1b')
                                elif da == 'M':
                                    self.ws[t].fill = PatternFill(patternType='solid', fgColor='f7ec02')
                                else:
                                    self.ws[t].fill = PatternFill(patternType='solid', fgColor='fcffc7')
            r += 1

        self.Add_end_data()

    # 添加第一行内容
    def Add_row(self):
        fir_row_list = []
        fir_row_list.append('对应关系')
        fir_row_list.append('1工程知识')
        for i in range((int(self.index_point_sum['1'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('2问题分析')
        for i in range((int(self.index_point_sum['2'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('3设计/开发解决方案')
        for i in range((int(self.index_point_sum['3'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('4研究')
        for i in range((int(self.index_point_sum['4'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('5使用现代工具')
        for i in range((int(self.index_point_sum['5'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('6工程与社会')
        for i in range((int(self.index_point_sum['6'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('7环境与可持续发展')
        for i in range((int(self.index_point_sum['7'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('8职业规范')
        for i in range((int(self.index_point_sum['8'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('9个人和团队')
        for i in range((int(self.index_point_sum['9'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('10沟通')
        for i in range((int(self.index_point_sum['10'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('11项目管理')
        for i in range((int(self.index_point_sum['11'])) - 1):
            fir_row_list.append('')
        fir_row_list.append('12终身学习')
        for i in range((int(self.index_point_sum['12'])) - 1):
            fir_row_list.append('')

        self.ws.append(fir_row_list)

    # 添加最后一行数据  统计每个指标点有多少课程对应
    def Add_end_data(self):
        end_list = ['指标点对应课程数']
        for idx in self.idx_cour_sum:
            end_list.append(self.idx_cour_sum[idx])
        self.ws.append(end_list)

    # 合并单元格
    def MergeCell(self):
        sta_col = 2
        end_col = sta_col + int(self.index_point_sum['1']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['2']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['3']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['4']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['5']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['6']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['7']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['8']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['9']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['10']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['11']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)
        sta_col = end_col + 1
        end_col = sta_col + int(self.index_point_sum['12']) - 1
        self.ws.merge_cells(start_column=sta_col, end_column=end_col, start_row=1, end_row=1)

    # 文字居中
    def TextCentered(self):
        alignment_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws_area = self.ws["A1:AZ78"]
        for i in ws_area:
            for j in i:
                j.alignment = alignment_center

    # 设置行，列宽和高
    def SetCellSize(self):
        self.ws.column_dimensions['A'].width = 40
        self.ws.row_dimensions[1].height = 50
        # ws.column_dimensions["B"].width = 30  # 设置B列宽度为30
        # ws.row_dimensions[2].height = 30  # 设置第2行高度为30

    # 自适应列宽
    def style_excel(self, sheet):
        # 获取最大行数与最大列数
        max_column = sheet.max_column
        max_row = sheet.max_row

        # 将每一列，单元格列宽最大的列宽值存到字典里，key:列的序号从1开始(与字典num_str_dic中的key对应)；value:列宽的值
        max_column_dict = {}

        # 生成列名字典，只是为了方便修改列宽时指定列，key:数字，从1开始；value:列名，从A开始
        num_str_dict = self.Get_num_colnum_dict()

        # 遍历全部列
        for i in range(1, max_column + 1):
            # 遍历每一列的全部行
            for j in range(1, max_row + 1):
                column = 0
                # 获取j行i列的值
                sheet_value = sheet.cell(row=j, column=i).value
                # 通过列表生成式生成字符列表，将当前获取到的单元格的str值的每一个字符放在一个列表中（列表中一个元素是一个字符）
                sheet_value_list = [k for k in str(sheet_value)]
                # 遍历当前单元格的字符列表
                for v in sheet_value_list:
                    # 判定长度，一个数字或一个字母，单元格列宽+=1.1，其它+=2.2（长度可根据需要自行修改，经测试一个字母的列宽长度大概为1）
                    if v.isdigit() == True or v.isalpha() == True:
                        column += 0.8
                    else:
                        column += 10
                # 当前单元格列宽与字典中的对比，大于字典中的列宽值则将字典更新。如果字典没有这个key，抛出异常并将值添加到字典中
                try:
                    if column > max_column_dict[i]:
                        max_column_dict[i] = column
                except Exception as e:
                    max_column_dict[i] = column
        # 此时max_column_dict字典中已存有当前sheet的所有列的最大列宽值，直接遍历字典修改列宽
        for key, value in max_column_dict.items():
            sheet.column_dimensions[num_str_dict[key]].width = value


if __name__ == '__main__':
    matrix = Matrix('D:/Project/Matrix/计算机科学与技术', '123')
    # 生成列名字典
    matrix.Get_num_colnum_dict()

    # 存储每个工作表的数据到course_content中
    matrix.GetCourseData()

    # 求二级指标点个数
    matrix.Get_index_point()

    # 添加数据到矩阵中
    matrix.AddContent()

    # 合并第一行的单元格
    matrix.MergeCell()

    # 设置文字居中
    matrix.TextCentered()
    matrix.SetCellSize()
    matrix.style_excel(matrix.ws)
    # matrix.wb.save(matrix.path + "Matrix.xlsx")
    # return (matrix.path + matrix.useid + "Matrix.xlsx")

